from rest_framework import serializers
from django.utils import timezone
from .models import DatabaseInstance, SQLRequest, SQLQueryHistory, SQLAuditLog
from user_management.models import CustomUser


class DatabaseInstanceSerializer(serializers.ModelSerializer):
    class Meta:
        model = DatabaseInstance
        fields = '__all__'
        extra_kwargs = {
            'password': {'write_only': True}
        }


class SQLRequestSerializer(serializers.ModelSerializer):
    requester_name = serializers.CharField(source='requester.username', read_only=True)
    reviewer_name = serializers.CharField(source='reviewer.username', read_only=True)
    executor_name = serializers.CharField(source='executor.username', read_only=True)
    database_instance_name = serializers.CharField(source='database_instance.name', read_only=True)
    status_display = serializers.CharField(source='get_status_display', read_only=True)
    priority_display = serializers.CharField(source='get_priority_display', read_only=True)
    request_type_display = serializers.CharField(source='get_request_type_display', read_only=True)

    # 添加嵌套对象序列化 - 重新定义这些字段以覆盖模型字段
    requester = serializers.SerializerMethodField()
    database_instance = serializers.SerializerMethodField()
    reviewer = serializers.SerializerMethodField()
    executor = serializers.SerializerMethodField()

    def get_requester(self, obj):
        if obj.requester:
            return {
                'id': obj.requester.id,
                'username': obj.requester.username,
                'full_name': obj.requester.full_name,
                'email': obj.requester.email
            }
        return None

    def get_database_instance(self, obj):
        if obj.database_instance:
            return {
                'id': obj.database_instance.id,
                'name': obj.database_instance.name,
                'host': obj.database_instance.host,
                'port': obj.database_instance.port,
                'database_type': obj.database_instance.database_type
            }
        return None

    def get_reviewer(self, obj):
        if obj.reviewer:
            return {
                'id': obj.reviewer.id,
                'username': obj.reviewer.username,
                'first_name': obj.reviewer.first_name,
                'last_name': obj.reviewer.last_name,
                'email': obj.reviewer.email
            }
        return None

    def get_executor(self, obj):
        if obj.executor:
            return {
                'id': obj.executor.id,
                'username': obj.executor.username,
                'first_name': obj.executor.first_name,
                'last_name': obj.executor.last_name,
                'email': obj.executor.email
            }
        return None

    class Meta:
        model = SQLRequest
        fields = [
            'id', 'title', 'description', 'sql_content', 'request_type', 'request_type_display',
            'database_instance', 'database_instance_name', 'requester', 'requester_name',
            'reviewer', 'reviewer_name', 'executor', 'executor_name', 'status', 'status_display',
            'priority', 'priority_display', 'review_comment', 'execution_result',
            'expected_execution_time', 'created_at', 'reviewed_at', 'executed_at', 'withdrawn_at'
        ]

    def create(self, validated_data):
        # 自动设置申请人为当前用户
        validated_data['requester'] = self.context['request'].user
        return super().create(validated_data)


class SQLRequestCreateSerializer(serializers.ModelSerializer):
    """创建SQL工单的序列化器"""
    
    class Meta:
        model = SQLRequest
        fields = [
            'title', 'description', 'sql_content', 'request_type',
            'database_instance', 'reviewer', 'priority', 'expected_execution_time'
        ]

    def create(self, validated_data):
        # 自动设置申请人为当前用户
        validated_data['requester'] = self.context['request'].user
        return super().create(validated_data)


class SQLRequestReviewSerializer(serializers.ModelSerializer):
    """审核SQL工单的序列化器"""
    
    class Meta:
        model = SQLRequest
        fields = ['status', 'review_comment']

    def validate_status(self, value):
        if value not in ['approved', 'rejected']:
            raise serializers.ValidationError("状态只能是 'approved' 或 'rejected'")
        return value

    def update(self, instance, validated_data):
        # 设置审核人和审核时间
        instance.reviewer = self.context['request'].user
        instance.reviewed_at = timezone.now()
        return super().update(instance, validated_data)


class SQLQueryHistorySerializer(serializers.ModelSerializer):
    user_name = serializers.CharField(source='user.username', read_only=True)
    database_instance_name = serializers.CharField(source='database_instance.name', read_only=True)

    class Meta:
        model = SQLQueryHistory
        fields = [
            'id', 'user', 'user_name', 'database_instance', 'database_instance_name',
            'sql_content', 'execution_time', 'rows_affected', 'is_success',
            'error_message', 'ip_address', 'user_agent', 'created_at'
        ]
        read_only_fields = ['user', 'ip_address', 'user_agent']


class SQLAuditLogSerializer(serializers.ModelSerializer):
    user_name = serializers.CharField(source='user.username', read_only=True)
    action_display = serializers.CharField(source='get_action_display', read_only=True)

    class Meta:
        model = SQLAuditLog
        fields = [
            'id', 'user', 'user_name', 'action', 'action_display',
            'target_type', 'target_id', 'description',
            'ip_address', 'user_agent', 'created_at'
        ]
        read_only_fields = ['user', 'ip_address', 'user_agent']


class AdminUserSerializer(serializers.ModelSerializer):
    """管理员用户序列化器（用于工单审核人选择）"""

    class Meta:
        model = CustomUser
        fields = ['id', 'username', 'full_name', 'email']
